package com.octopus.optiworks.dao;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.MissingResourceException;
import java.util.ResourceBundle;
import java.util.StringTokenizer;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.stereotype.Component;

import com.octopus.optiworks.action.StepActionUtil;
import com.octopus.optiworks.controller.SearchCommand;
import com.octopus.optiworks.model.Product;

@Component
public class ProductDao extends GenericHibernateDao<Product, Long>{
	
	private static Log log = LogFactory.getLog(ProductDao.class);
	
	public Product loadAll(Long id){
		List<Product> list = super.find("from Product p left join fetch p.step10_1Datas left join fetch p.step10_2Datas left join fetch p.step20Datas left join fetch p.step30_1Datas left join fetch p.step30_2Datas" +
				" left join fetch p.step40Datas left join fetch p.step50Datas left join fetch p.step60Datas left join fetch p.step70Datas left join fetch p.step80Datas left join fetch p.step90Datas" +
				" left join fetch p.step100_1Datas left join fetch p.step100_2Datas left join fetch p.step100_3Datas left join fetch p.step110Datas left join fetch p.step120_1Datas left join fetch p.step120_2Datas" +
				" left join fetch p.step130Datas left join fetch p.step140_1Datas left join fetch p.step140_2Datas left join fetch p.step150Datas left join fetch p.step160Datas" +
				" left join fetch p.step170_1Datas left join fetch p.step170_2Datas left join fetch p.step180Datas" +
				" left join fetch p.dataSheet where p.id=?", new Long[]{id});
		return null==list?null:list.get(0);
	}
	
	public Product getProductByProcess(String processInstanceId){
		List<Product> list = super.find("from Product p where p.processId=?", new String[]{processInstanceId});
		if(list != null && list.size() > 0)
			return list.get(0);
		return null;
	}
	
	public List<Product> getProductsByLot(String lot){
		List<Product> list = super.find("from Product p where p.lot=?", new String[]{lot});
		return list;
	}
	
	public int getProductsBySearchCount(SearchCommand search){
		int number = 0;
		StringBuffer sql = new StringBuffer("select count(p) from Product p");
		List<Object> values = new ArrayList<Object>();
		if(search == null){
			number = super.count(sql.append(" where p.id > 0").toString(), values.toArray());
			search = new SearchCommand();
			search.setTotal(number);
			return number;
		}
		if(search.getPermissons() != null && search.getPermissons().size() == 0){
			search.setTotal(0);
			return 0;
		}
		this.prepareSQL(search, sql, values);
		number = super.count(sql.toString(), values.toArray());
		search.setTotal(number);
		return number;
	}
	
	public List<Product> getProductsBySearch(SearchCommand search){
		List<Product> list = null;
		StringBuffer sql = new StringBuffer("select p from Product p");
		List<Object> values = new ArrayList<Object>();
		if(search == null)
			return super.loadAll();
		if(search.getPermissons() != null && search.getPermissons().size() == 0)
			return new ArrayList<Product>();
		this.prepareSQL(search, sql, values);
		if(search.getOffset() != null && search.getLength() != null)
			list = super.find(sql.toString(), values.toArray(), search.getOffset(), search.getLength());
		else
			list = super.find(sql.toString(), values.toArray());
		return list==null?new ArrayList<Product>():list;
	}
	
	public void archiveProducts(SearchCommand search){
		StringBuffer sql = new StringBuffer("update Product pp set pp.archive=? where pp.id in (select p.id from Product p");
		List<Object> values = new ArrayList<Object>();
		values.add(Boolean.TRUE);
		if(search != null)
			this.prepareSQL(search, sql, values);
		sql.append(")");
		super.bulkUpdate(sql.toString(), values.toArray());
	}
	
	public void unarchiveProducts(SearchCommand search){
		StringBuffer sql = new StringBuffer("update Product pp set pp.archive=? where pp.id in (select p.id from Product p");
		List<Object> values = new ArrayList<Object>();
		values.add(Boolean.FALSE);
		if(search != null)
			this.prepareSQL(search, sql, values);
		sql.append(")");
		super.bulkUpdate(sql.toString(), values.toArray());
	}
	
	private void prepareSQL(SearchCommand search, StringBuffer sql, List<Object> values){
		StringBuffer condition = new StringBuffer(" where p.id > 0");
		//Check fuzzy search
		if(search.getKeyword() != null && search.getKeyword().trim().length() > 0){
			String value = null;
			Map<String, List<String>> map = null;
			ResourceBundle configuation = ResourceBundle.getBundle("configuration", Locale.getDefault());
			try{
				value = configuation.getString("search.keywords");
				if(value != null && value.trim().length() > 0){
					map = new HashMap<String, List<String>>();
					StringTokenizer st = new StringTokenizer(value, ",");
					while(st.hasMoreTokens()){
						String fetch = st.nextToken().trim();
						String table = fetch.substring(0, fetch.indexOf("."));
						table = table.equals("product")?"p":table;
						String column = fetch.substring(fetch.indexOf(".") + 1);
						List<String> columns = null;
						if(map.containsKey(table))
							columns = map.get(table);
						else{
							columns = new ArrayList<String>();
							map.put(table, columns);
						}
						if(!columns.contains(column))
							columns.add(column);
					}
				}
			}catch(MissingResourceException e){
				log.warn("Missing configuration for the key: 'search.keywords'");
			}
			if(map != null){
				for(String table : map.keySet()){
					if(!table.equals("p")){
						sql.append(" left join p." + table + "Datas " + table);
					}
					for(String column : map.get(table)){
						condition.append(" or lower(" + table + "." + column + ") like lower(?)");
						values.add("%" + search.getKeyword() + "%");
					}
				}
				if(condition.indexOf("or") > 0){
					condition.replace(condition.indexOf("or"), condition.indexOf("or") + 2, "and (");
					condition.append(")");
				}
				
			}
		}
		//Check operator
		if(search.getOperator() != null && !search.getOperator().equals("all")){
			String[] tables = new String[]{"step10_1", "step10_2", "step20", "step30_1", "step30_2", "step40", "step50", "step60", "step70", "step80", "step90",
					 "step100_1", "step100_2", "step100_3", "step110", "step120_1", "step120_2", "step130", "step140_1", "step140_2", "step150", "step160", "step170_1", "step170_2", "step180"};
			StringBuffer sb = new StringBuffer(" and (");
			for(String table : tables){
				if(sql.indexOf(table + "Datas") < 0)
					sql.append(" left join p." + table + "Datas " + table);
				sb.append(" " + table + ".operator=? or");
				values.add(search.getOperator());
			}
			sb.delete(sb.lastIndexOf(" or"), sb.length());
			sb.append(")");
			condition.append(sb);
		}
		//Filter status
		if(search.getStep() != null && !search.getStep().equals(StepActionUtil.STEP_ALL) 
				&& !search.getStep().equals(StepActionUtil.TESTRESULT_FAILURE) 
				&& !search.getStep().equals(StepActionUtil.TESTRESULT_WAIVE)){
			condition.append(" and (");
			condition.append(" p.status=? or");
			condition.append(" p.status=? or");
			condition.append(" p.status=?");
			values.add(search.getStep());
			values.add(StepActionUtil.toWaiveFail(search.getStep(), StepActionUtil.TESTRESULT_FAILURE));
			values.add(StepActionUtil.toWaiveFail(search.getStep(), StepActionUtil.TESTRESULT_WAIVE));
			
			condition.append(")");
		}else if(search.getStep() != null 
				&& (search.getStep().equals(StepActionUtil.TESTRESULT_FAILURE) || search.getStep().equals(StepActionUtil.TESTRESULT_WAIVE))){
			condition.append(" and (");
			condition.append(" p.status like ?");
			values.add("%" + search.getStep() + "%");
			condition.append(")");
		}
		//Archive
		if(true){
			condition.append(" and (");
			condition.append(" p.archive=?");
			values.add(search.getArchive());
			condition.append(")");
		}
		//Filter Final SN
		if(search.getFinalSn() != null && search.getFinalSn().trim().length() > 0){
			condition.append(" and (");
			condition.append(" p.finalSn=?");
			values.add(search.getFinalSn());
			condition.append(")");
		}
		//Filter Internal SN
		if(search.getInternalSn() != null && search.getInternalSn().trim().length() > 0){
			condition.append(" and (");
			condition.append(" p.internalSn=?");
			values.add(search.getInternalSn());
			condition.append(")");
		}
		//Filter PN
		if(search.getPn() != null && search.getPn().trim().length() > 0){
			condition.append(" and (");
			condition.append(" p.pn=?");
			values.add(search.getPn());
			condition.append(")");
		}
		//Date
		if(search.getBeginDate() != null){
			condition.append(" and (");
			condition.append(" p.createDate>=?");
			values.add(search.getBeginDate());
			condition.append(")");
		}
		if(search.getEndDate() != null){
			condition.append(" and (");
			condition.append(" p.createDate<=?");
			values.add(search.getEndDate());
			condition.append(")");
		}
		//Check permission
		if(search.getPermissons() != null){
			condition.append(" and (");
			for(String permission : search.getPermissons()){
				condition.append(" p.status=? or");
				condition.append(" p.status=? or");
				condition.append(" p.status=? or");
				values.add(permission);
				values.add(StepActionUtil.toWaiveFail(permission, StepActionUtil.TESTRESULT_FAILURE));
				values.add(StepActionUtil.toWaiveFail(permission, StepActionUtil.TESTRESULT_WAIVE));
			}
			condition.delete(condition.lastIndexOf(" or"), condition.length());
			condition.append(")");
		}
		
		sql.append(condition);
		log.debug("Get Products by Step SQL: " + sql.toString());
		log.debug(values);
	}
	
	public void updateStatus(Long productId, String status){
		super.bulkUpdate("update Product p set p.status=? where p.id=?", new Object[]{status, productId});
	}
	
	public void updateSn(Long productId, String sn){
		super.bulkUpdate("update Product p set p.finalSn=? where p.id=?", new Object[]{sn, productId});
	}
	
	public void updateLockedBy(Long productId, String user){
		super.bulkUpdate("update Product p set p.lockedBy=? where p.id=?", new Object[]{user, productId});
	}
	
	public void synLotProperties(Product product, List<String> properties){
		if(product.getLot() == null || product.getLot().length() == 0 || properties == null || properties.size() == 0)
			return;
		Field[] fields = Product.class.getDeclaredFields();
		List<Object> values = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer("update Product p set ");
		for(Field field : fields){
			if(!properties.contains(field.getName()))
				continue;
			sql.append(field.getName());
			sql.append("=");
			sql.append("?");
			
			String fieldName=field.getName();   
            String stringLetter=fieldName.substring(0, 1).toUpperCase(); 
            String getName="get"+stringLetter+fieldName.substring(1); 
            try {
				Method getMethod= Product.class.getMethod(getName, new Class[]{});
				values.add(getMethod.invoke(product, new Object[]{}));
			} catch (SecurityException e) {
				e.printStackTrace();
			} catch (NoSuchMethodException e) {
				e.printStackTrace();
			} catch (IllegalArgumentException e) {
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			} catch (InvocationTargetException e) {
				e.printStackTrace();
			}
			
			sql.append(",");
		}
		sql.delete(sql.lastIndexOf(","), sql.length());
		sql.append(" where p.lot=?");
		values.add(product.getLot());
		log.debug("Syn lot properties SQL: " + sql.toString());
		super.bulkUpdate(sql.toString(), values.toArray());
	}

}
